<?php
/*
 * @param auther: feyeen Date: 2016/5/17 Time: 10:23
 * @param StatisticController.php
 */
namespace backend\controllers;


use Yii;
use yii\base\Controller;
use services\models\OrderGoods;
use services\models\OrderInfo;
use services\models\Goods;
use services\models\ProductDetail;
use services\models\Product;
use common\models\tmall1\GSupplierInfo;
use backend\models\StatisticSearch;


class StatisticController extends Controller{

    //测试控制器
    public function actionIndex(){

        $timeArr     = Yii::$app->request->get('StatisticSearch');

        $searchModel = new StatisticSearch();
        $searchModel->start_time    = empty($timeArr['start_time'])? date('Y-m-d H:s',time(-1)):$timeArr['start_time'];
        $searchModel->end_time      = empty($timeArr['end_time'])? date('Y-m-d H:s',time()):$timeArr['end_time'];

        //查询数据
        if(Yii::$app->getRequest()->get()){
            $params['start_time']   = $searchModel->start_time;
            $params['end_time']     = $searchModel->end_time;

            $dataInfo  = $searchModel->search($params);
        }
        //print_r($dataInfo);exit;
        return $this->render('index', [
            'searchModel'   => $searchModel,
            'dataInfo'      => $dataInfo,
        ]);

    }

    //a ctiona
    public function actionExplort(){

        @set_time_limit(0);                  //防止超时
        $timeArr['start_time']   = Yii::$app->request->get('start_time');
        $timeArr['end_time']     = Yii::$app->request->get('end_time');

        $params['start_time']    = empty($timeArr['start_time'])? date('Y-m-d H:s',time(-1)):$timeArr['start_time'];
        $params['end_time']      = empty($timeArr['end_time'])? date('Y-m-d H:s',time()):$timeArr['end_time'];

        //查询时间段相关的订单
        $strIn      = StatisticSearch::getOrder($params);

        //查询时间段的所有商品
        $orderGood  = OrderGoods::find()
            ->select(['order_goods.goods_id','goods_detail.goods_supply_id','order_goods.product_id','sum(order_goods.buy_number) as buy_number','order_goods.order_info_id'])
            ->joinWith('goodsDetail',true,'LEFT JOIN')
            ->andOnCondition(['in', 'order_goods.order_info_id',$strIn])
            ->asArray()
            ->groupBy(['order_goods.product_id'])
            ->all();

        //查寻相关的名称redis 购买数量
        if(count($orderGood) > 0){
            foreach($orderGood as $key=>&$val){
                //取商品详情
                $val['goodPic']  = Goods::findByGoodsIdFromRedis($val['goods_id'],['goods_name']);
                //取规格详情
                $val['products'] = ProductDetail::findByProductIdFromRedis($val['product_id'],['spec_desc']);
                //查询使用和购买信息
                $val['Product']  = Product::findByProductId($val['product_id']);
                //查询供应商
                $val['name']     = GSupplierInfo::find()->select(['name'])->where(['uid'=>$val['goods_supply_id']])->asArray()->one();
            }
        }


        //实例化
        $objectPHPExcel = new \PHPExcel();
        $objectPHPExcel->setActiveSheetIndex(0);

        $objectPHPExcel->getActiveSheet()->mergeCells('A1:J1');
        $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('A1',$params['start_time'].'--'.$params['end_time'].'订单商品汇总')->getStyle('A1:J1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getFont()->setSize(14);

        //表格头的输出
        $objectPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
        $objectPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
        $objectPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(35);
        $objectPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
        $objectPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
        $objectPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10);
        $objectPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
        $objectPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
        $objectPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);
        $objectPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(27);

        $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('A2','商品ID');
        $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('B2','产品ID');
        $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('C2','商品名称');
        $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('D2','规格');
        $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('E2','是否售卖');
        $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('F2','是否试用');
        $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('G2','售卖价格');
        $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('H2','试用价格');
        $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('I2','数量');
        $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('J2','供应商名称');

        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('A2')->getFont()->setSize(12);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('B2')->getFont()->setSize(12);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('B2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('C2')->getFont()->setSize(12);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('C2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('D2')->getFont()->setSize(12);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('D2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('E2')->getFont()->setSize(12);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('E2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('F2')->getFont()->setSize(12);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('F2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('G2')->getFont()->setSize(12);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('G2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('H2')->getFont()->setSize(12);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('H2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('I2')->getFont()->setSize(12);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('I2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('J2')->getFont()->setSize(12);
        $objectPHPExcel->setActiveSheetIndex(0)->getStyle('J2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //处理数据

        $key =3;

        if(count($orderGood) > 0){
            foreach($orderGood as $vals){
                $sale       = '否';
                $try        = '否';
                if(isset($vals['Product']['type'])      && 1 == $vals['Product']['type']){
                    $sale   ='是';
                }elseif(isset($vals['Product']['type']) && 2 == $vals['Product']['type']){
                    $try    ='是';
                }
                //填写数据
                if(isset($vals['Product']['sale_price']) || isset($vals['Product']['try_price'])){
                    $sale_price = $vals['Product']['sale_price'];
                    $try_price  = $vals['Product']['try_price'];
                }else{
                    $sale_price ='0.00';
                    $try_price  ='0.00';
                }

                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$key,$vals['goods_id']);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$key,$vals['product_id']);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$key,$vals['goodPic']['goods_name']);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$key,$vals['products']['spec_desc']);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$key,$sale);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$key,$try);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$key,$sale_price);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$key,$try_price);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('I'.$key,$vals['buy_number']);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('J'.$key,$vals['name']['name']);
                $key++;
            }
        }


        $objectPHPExcel->getActiveSheet()->getPageSetup()->setHorizontalCentered(true);
        $objectPHPExcel->getActiveSheet()->getPageSetup()->setVerticalCentered(false);

        ob_end_clean();
        ob_start();
        header('Content-Type : application/vnd.ms-excel');
        header('Content-Disposition:attachment;filename="'.'产品信息表-'.date("Y年m月j日").'.xls"');
        $objWriter= \PHPExcel_IOFactory::createWriter($objectPHPExcel,'Excel5');
        $objWriter->save('php://output');
    }
}
